/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package net.hnilice.cisteniulic.excel;

import net.hnilica.cisteniulic.dto.TerminExcel;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import java.util.TreeSet;
import java.util.logging.Level;
import java.util.logging.Logger;
import net.hnilica.cisteniulic.exception.ParsingExcelMyException;
import org.apache.poi.OldFileFormatException;
import org.apache.poi.hssf.OldExcelFormatException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 *
 * @author swosh
 */
public class Parser {

    protected Logger log = Logger.getLogger(getClass().getName());

    public Collection parseOldExcelFile(InputStream is) throws ParsingExcelMyException {

        HSSFWorkbook wb = null;
        try {
            wb = new HSSFWorkbook(is);
        } catch (IOException ex) {
            Logger.getLogger(ExcelEngine.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalArgumentException ex) {
            Logger.getLogger(ExcelEngine.class.getName()).log(Level.SEVERE, null, ex);
            throw new ParsingExcelMyException();
        }
        List<TerminExcel> listTerminExcel = new ArrayList();
        int numWb = -1;
            if (wb != null) {
              numWb  = wb.getNumberOfSheets();
            }
        for (int s = 0; s < numWb; s++) {
            HSSFSheet sheet = wb.getSheetAt(s);
            int numR = -1;
            if (sheet != null) {
                numR = sheet.getPhysicalNumberOfRows();
            }
            for (int r = 0; r < numR; r++) {//row and cell must start from 0 beacuse cell reference is orientated from 0 index
                if (r != 0) { // Dont read first row....there is names of column
                    HSSFRow row = sheet.getRow(r);
                    TerminExcel terminExcel = new TerminExcel();
                    terminExcel.setRow(r + 1); // set row from 1
                    terminExcel.setSheet(s + 1); // set sheet from 1
                    int numC = 7;
                    //If non null row contains non blank cell else numC = 0
                    boolean isBlank = true;
                    if (row != null) {
                        int numCTest = row.getPhysicalNumberOfCells();
                        for (int i = 0; i < numCTest; i++) {
                            HSSFCell cell = row.getCell(i);
                            if (cell != null || !(cell.getCellType() == HSSFCell.CELL_TYPE_BLANK)) {
                                isBlank = false;
                                break;
                            }
                        }
                    }
                    if (isBlank) {
                        break;
                    }
                    for (int c = 0; c <= numC; c++) {
                        HSSFCell cell = row.getCell(c);

                        String ref = new CellReference(r, c).formatAsString();
                        switch (c) {
                            case 0:
                                if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                                    String nazUlice = cell.getStringCellValue().trim();
                                    terminExcel.setNazUlice(nazUlice);
                                } else {
                                    terminExcel.setNazUliceInfo("Údaj v pozici " + ref + " na listu " + terminExcel.getSheet() + " není řetězec. ");
                                }
                                break;
                            case 1:
                                if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                        Date terCis = cell.getDateCellValue();
                                        terminExcel.setTerCis(terCis);
                                    } else {
                                        terminExcel.setTerCisInfo("Údaj v pozici " + ref + " na listu " + terminExcel.getSheet() + " není v požadovaném datum formátu dd.MM.yyyy. ");
                                    }
                                } else if (cell != null || cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                                    String terCisS = cell.getStringCellValue();
                                    SimpleDateFormat pattern = new SimpleDateFormat("dd.MM.yyyy");
                                    try {
                                        Date terCis = pattern.parse(terCisS);
                                        terminExcel.setTerCis(terCis);
                                    } catch (ParseException ex) {
                                        Logger.getLogger(Parser.class.getName()).log(Level.SEVERE, null, ex);
                                    }

                                } else {
                                    terminExcel.setTerCisInfo("Údaj v pozici " + ref + " na listu " + terminExcel.getSheet() + " není v požadovaném datum formátu dd.MM.yyyy. ");
                                }
                                break;
                            case 2:
                                if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                                    String nazAkce = cell.getStringCellValue().trim();
                                    terminExcel.setNazAkce(nazAkce);
                                } else {
                                    terminExcel.setNazAkceInfo("Údaj v pozici " + ref + " na listu " + terminExcel.getSheet() + " není řetězec. ");
                                }
                                break;

                            case 3:
                                if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                                    String cisOriCellS = cell.getStringCellValue().trim();
                                    cisOriCellS = cisOriCellS.replaceAll("[^\\,\\-\\d]", "");
                                    int difference = 0;
                                    int second;
                                    int first;
                                    Set<Integer> treeSetCisOri = new TreeSet<Integer>();
                                    String[] cisOriSA = cisOriCellS.split(",");

                                    for (int i = 0; cisOriSA.length > i; i++) {
                                        String cisOriS = cisOriSA[i];
                                        if (cisOriS.contains("-")) {
                                            String[] cisOrisS2SA = cisOriS.split("-");
                                            if (cisOrisS2SA.length > 2) {
                                                terminExcel.setCisOrientacniInfo("Údaj v pozici " + ref + " na listu " + terminExcel.getSheet() + " obsahuje více než jednu pomlčku."
                                                        + " Povolen je tvar např \"1-21\", ale ne \"1-21-22\". ");
                                            }
                                            first = Integer.parseInt(cisOrisS2SA[0]);
                                            second = Integer.parseInt(cisOrisS2SA[1]);
                                            difference = second - first;
                                            for (int j = 0; difference > j; j++) {
                                                // j + first e.g. 0 + 5 is first, 0 + 5 is second
                                                treeSetCisOri.add(Integer.valueOf(j + first));
                                            }
                                            treeSetCisOri.add(second);



                                        } else {
                                            treeSetCisOri.add(Integer.valueOf(cisOriS).intValue());
                                        }

                                    }
                                    int k = 0;
                                    Set<Integer> cisOrientacni = new HashSet<Integer>();
                                    for (Iterator<Integer> it = treeSetCisOri.iterator(); it.hasNext();) {
                                        Integer integer = it.next();
                                        cisOrientacni.add(integer);

                                    }
                                    //empty before next loop
                                    treeSetCisOri.clear();
                                    terminExcel.setCisOrientacni(cisOrientacni);

                                } else if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                                    Set<Integer> cisOrientacni = new HashSet<Integer>();
                                    cisOrientacni.add((int)cell.getNumericCellValue());
                                    terminExcel.setCisOrientacni(cisOrientacni);
                                } else {
                                    terminExcel.setCisOrientacniInfo("Údaj v pozici " + ref + " na listu " + terminExcel.getSheet() + " není řetězec ani číslo. ");
                                }
                                break;
                            case 4:
                                if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                                    String newNazUlice = cell.getStringCellValue().trim();
                                    terminExcel.setNewNazUlice(newNazUlice);
                                } else {
                                    terminExcel.setNewNazUliceInfo("Údaj v pozici " + ref + " na listu " + terminExcel.getSheet() + " není řetězec. ");
                                }
                                break;
                            case 5:
                                if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                        Date newTerCis = cell.getDateCellValue();
                                        terminExcel.setNewTerCis(newTerCis);
                                    } else {
                                        terminExcel.setNewTerCisInfo("Údaj v pozici " + ref + " na listu " + terminExcel.getSheet() + " není v požadovaném datum formátu dd.MM.yyyy. ");
                                    }
                                } else if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                                    String newTerCisS = cell.getStringCellValue();
                                    SimpleDateFormat pattern = new SimpleDateFormat("dd.MM.yyyy");
                                    try {
                                        Date newTerCis = pattern.parse(newTerCisS);
                                        terminExcel.setNewTerCis(newTerCis);
                                    } catch (ParseException ex) {
                                        Logger.getLogger(Parser.class.getName()).log(Level.SEVERE, null, ex);
                                    }

                                } else {
                                    terminExcel.setNewTerCisInfo("Údaj v pozici " + ref + " na listu " + terminExcel.getSheet() + " není v požadovaném datum formátu dd.MM.yyyy. ");
                                }
                                break;
                            case 6:
                                if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                                    String newNazAkce = cell.getStringCellValue().trim();
                                    terminExcel.setNewNazAkce(newNazAkce);
                                } else {
                                    terminExcel.setNewNazAkceInfo("Údaj v pozici " + ref + " na listu " + terminExcel.getSheet() + " není řetězec. ");
                                }
                                break;

                            case 7:
                                if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                                    String newCisOriCellS = cell.getStringCellValue().trim();
                                    newCisOriCellS = newCisOriCellS.replaceAll("[^\\,\\-\\d]", "");
                                    int difference = 0;
                                    int second;
                                    int first;
                                    Set<Integer> treeSetCisOri = new TreeSet<Integer>();
                                    String[] newCisOriSA = newCisOriCellS.split(",");

                                    for (int i = 0; newCisOriSA.length > i; i++) {
                                        String newCisOriS = newCisOriSA[i];
                                        if (newCisOriS.contains("-")) {
                                            String[] newCisOrisS2SA = newCisOriS.split("-");
                                            if (newCisOrisS2SA.length > 2) {
                                                terminExcel.setNewCisOrientacniInfo("Údaj v pozici " + ref + " na listu " + terminExcel.getSheet() + " obsahuje více než jednu pomlčku."
                                                        + " Povolen je tvar např \"1-21\", ale ne \"1-21-22\". ");
                                            }
                                            first = Integer.parseInt(newCisOrisS2SA[0]);
                                            second = Integer.parseInt(newCisOrisS2SA[1]);
                                            difference = second - first;
                                            for (int j = 0; difference > j; j++) {
                                                // j + first e.g. 0 + 5 is first, 0 + 5 is second
                                                treeSetCisOri.add(Integer.valueOf(j + first));
                                            }
                                            treeSetCisOri.add(second);



                                        } else {
                                            treeSetCisOri.add(Integer.valueOf(newCisOriS).intValue());
                                        }

                                    }
                                    int k = 0;
                                    Set<Integer> newCisOrientacni = new HashSet<Integer>();
                                    for (Iterator<Integer> it = treeSetCisOri.iterator(); it.hasNext();) {
                                        Integer integer = it.next();
                                        newCisOrientacni.add((int)integer.intValue());

                                    }
                                    //empty before next loop
                                    treeSetCisOri.clear();
                                    terminExcel.setNewCisOrientacni(newCisOrientacni);

                                } else if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                                    Set<Integer> cisNewOrientacni = new HashSet<Integer>();
                                    cisNewOrientacni.add((int) cell.getNumericCellValue());
                                    terminExcel.setNewCisOrientacni(cisNewOrientacni);
                                } else {
                                    terminExcel.setNewCisOrientacniInfo("Údaj v pozici " + ref + " na listu " + terminExcel.getSheet() + " není řetězec ani číslo. ");
                                }
                                break;
                            default:
                                break;
                            //Ignore
                            }

                    }
                    listTerminExcel.add(terminExcel);
                }

            }

        }
        return listTerminExcel;
    }

}
